package com.xinhe.web.reposity;

import com.xinhe.web.entity.Order;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

public interface OrderRepository  extends JpaRepository<Order, Long> {

    @Modifying
    @Query(value = "update tb_order set status = ?2 where removed = 0 and id = ?1", nativeQuery = true)
    void updateStatusById(Long orderId, String timeout);

    @Query(value = "select * from tb_order where status = 'UN_PAY'  and TIMESTAMPDIFF(MINUTE,create_time,NOW())>?1", nativeQuery = true)
    List<Order> findByTimeout(Integer teamTimeoutMinuteStr);
    @Query(value = " select * from tb_order where removed = 0 and status ='SENDED' and send_time <= date_sub(now(),interval 7 day)", nativeQuery = true)
    List<Order> findSevenDaySendOrder();
    @Query(value = "select * from tb_order where f_seller_user_id=?1", nativeQuery = true)
    List<Order> findBySellerShopUserId(Long sellerUserId);
    @Query(value = "select ifnull(sum(order_pay_fee),0) from tb_order where removed=0 and status in ('PAYED','SENDED','RECEIVED') and f_seller_user_id=?1 and to_days(create_time)=to_days(now())", nativeQuery = true)
    BigDecimal findBySellerUserId1(Long aLong);
    @Query(value = "select ifnull(sum(order_pay_fee),0) from tb_order where removed=0 and status in ('PAYED','SENDED','RECEIVED') and f_seller_user_id=?1 and TO_DAYS( NOW( ) ) - TO_DAYS( create_time) = 1", nativeQuery = true)
    BigDecimal findBySellerUserId2(Long aLong);
    @Query(value = "select ifnull(sum(order_pay_fee),0) from tb_order where removed=0 and status in ('PAYED','SENDED','RECEIVED') and f_seller_user_id=?1 and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time)", nativeQuery = true)
    BigDecimal findBySellerUserId3(Long aLong);

    @Query(value = "select count(1) from tb_order where removed=0 and status in ('PAYED','SENDED','RECEIVED') and f_seller_user_id=?1 and to_days(create_time)=to_days(now()) ", nativeQuery = true)
    Integer findBySellerOrderCount1(Long aLong);
    @Query(value = "select count(1) from tb_order where removed=0 and status in ('PAYED','SENDED','RECEIVED') and f_seller_user_id=?1 and TO_DAYS( NOW( ) ) - TO_DAYS( create_time) = 1 ", nativeQuery = true)
    Integer findBySellerOrderCount2(Long aLong);
    @Query(value = "select count(1) from tb_order where removed=0 and status in ('PAYED','SENDED','RECEIVED') and f_seller_user_id=?1 and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time)", nativeQuery = true)
    Integer findBySellerOrderCount3(Long aLong);

    @Query(value = "select sum(order_pay_fee)  from tb_order where removed=0 and status in ('PAYED','SENDED','RECEIVED','COMMENTED','SUCCESS') and f_seller_user_id=?1 and to_days(create_time) = to_days(now()) ", nativeQuery = true)
    BigDecimal findByToDayMoney(Long aLong);
    @Query(value = "select sum(order_pay_fee)  from tb_order where removed=0 and status in ('PAYED','SENDED','RECEIVED','COMMENTED','SUCCESS') and f_seller_user_id=?1 and DATE_FORMAT(create_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )", nativeQuery = true)
    BigDecimal findByMonth(Long aLong);

    @Query(value = "select date_format(create_time,'%Y-%m') time,sum(order_pay_fee) money from tb_order where removed = 0 and status in('PAYED','SENDED','RECEIVED','COMMENTED','SUCCESS') and f_seller_user_id = ?2 and to_days(create_time) >= to_days(?1)", nativeQuery = true)
    List<Map<String,Object>> findOrderIncomeGroup1(String lastTime, Long aLong);
    @Query(value = "select date_format(create_time,'%Y-%m-%d') time,sum(order_pay_fee) money from tb_order where removed = 0 and status in('PAYED','SENDED','RECEIVED','COMMENTED','SUCCESS') and f_seller_user_id = ?2 and to_days(create_time) >= to_days(?1)", nativeQuery = true)
    List<Map<String,Object>> findOrderIncomeGroup2(String lastTime, Long aLong);
}
